How to create and install AddIn in MS Access using VBA code.
This article is about to create and install addin in other MS Access applications. For implementation of AddIn we are using USysRegInfo. USysRegInfo is used for creating AddIn and then installing in other MS Access applications. There are two different types of add-ins: application-specific add-ins and Component Object Model add-ins. COM add-ins can be used across all Microsoft Office applications. However, they are not as easy to build and to distribute. An application-specific add-in works in only one application. According to requirement we can use one of them.
In AddIn Manager, new entries are created in the Windows Registry. The values for these keys are stored in the USysRegInfo table. The first created record is stored in 0 positions in the Type field and blanks in the ValName and Value fields. For implementation of this firstly we have to create the form with given fields and one for sub key. Then embed the Sub Form with following fields as shown in Fig 1.1. The record source for this Form is:
"SELECT USysRegInfo.Subkey FROM USysRegInfo GROUP BY USysRegInfo.Subkey". In highlighted sub key field, record source is Sub key from above query.
Fig:-1.1
Next is to create the Form which will work like sub form for add info manager form. The record source for this form is "SELECT USysRegInfo.ValName, USysRegInfo.Value FROM USysRegInfo WHERE (((USysRegInfo.ValName) Is Not Null)) ORDER BY USysRegInfo.ValName".
For value name textbox the record source is ValName and for value textbox the record source is Value as shown in Fig 1.2.
Fig:-1.2
Now open the add in info manager form in Form view as shown in Fig 1.3. Now we can type the information in the fields.
Fig:-1.3
Now we can use this addin in multiple Microsoft Office application by placing the addin Form in "C:\Users\admin\AppData\Roaming\Microsoft\AddIns" according to system settings. For testing of this addin we have to open other MS Access application. Go in the Database ribbon and select the AddIn and click on Add New, above specify path automatically opened. Then install this addin as shown in Fig 1.4.
Fig:-1.4
VBA Code for Microsoft Access Class Objects Form_AddInInfoManager:
Option Compare Database Option Explicit Public Function GetSummaryInfoProperty(strProperty As String) As Variant GetSummaryInfoProperty = CurrentDb.Containers("Databases") .Documents("SummaryInfo") .Properties(strProperty) End Function Public Function SetSummaryInfoProperty(strProperty As String, strValue As String) CurrentDb.Containers("Databases") .Documents("SummaryInfo") .Properties(strProperty) = strValue End Function Private Sub txtCompany_AfterUpdate() Call SetSummaryInfoProperty `("description", Me.txtCompany) End Sub Private Sub txtTitle_AfterUpdate() Call SetSummaryInfoProperty("name", Me.txtTitle) Dim strTitle As String strTitle = Me.txtTitle If strTitle = "" Then Exit Sub End If Call UpdateSubkey(strTitle) Me.Form.Requery End Sub Public Function UpdateSubkey(strValue As String) Dim strSQL As String Dim strSubkey As String strSubkey = "HKEY_CURRENT_ACCESS_ PROFILE\Menu Add-ins\" & strValue strSQL = "UPDATE USysRegInfo SET USysRegInfo.Subkey = '" & strSubkey & "';" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True End Function